/*
 * $Id: codetemplates.xml ver yyyy-mm-dd rb $
 */
package com.qy.util;

import java.io.File;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;


import com.qy.wap.bean.Column;
import com.qy.wap.bean.Table;

/**
 * 
 * 
 * @author ts-biao.zhang
 */
public class XmlReader {

	@SuppressWarnings("unchecked")
	public List<String> getCreateSql(String fileName) throws DocumentException
    {
        List<Table> loginTables = new ArrayList<Table>();
        SAXReader sAXReader = new SAXReader();
        Document document = sAXReader.read(new File(fileName));
        Iterator entries = document.getRootElement().elementIterator("UserForm");
        while(entries.hasNext())
        {
            Element form = (Element)entries.next();
            Iterator triggers = form.elementIterator("trigger");
            while(triggers.hasNext())
            {
                Element trigger = (Element)triggers.next();
                Iterator logins = trigger.elementIterator();
                while(logins.hasNext())
                {
                    Element login = (Element)logins.next();
                    Table loginTable = new Table();
                    loginTable.setName(login.elementText("code"));
                    List<Column> columns  = new ArrayList<Column>();
                    
                    Iterator conditions = login.elementIterator("conditions");
                    while(conditions.hasNext())
                    {
                        Element condition = (Element)conditions.next();
                        Iterator realconditions = condition.elementIterator("condition");
                        while(realconditions.hasNext())
                        {
                            Element realcondition = (Element)realconditions.next();
                            Column column = new Column();
                            column.setName(realcondition.attributeValue("inspect"));
                            column.setLength(getLength(column.getName()));
                            column.setType(getType(column.getName()));
                            columns.add(column);
                        }
                    }
                    loginTable.setColumns(columns);
                    loginTables.add(loginTable);
                }
            }
        } 
        
        List<String> sqls = new ArrayList<String>();
        for(int i=0;i<loginTables.size();i++)
        {
        	sqls.add(loginTables.get(i).getCreateString());
        }
        return sqls;
    }
    
    @SuppressWarnings("unchecked")
	public List<String> getInsertSql(String tableName,String fileName) throws DocumentException
    {
    	List<String> sqls = new ArrayList<String>();
    	SAXReader sAXReader = new SAXReader();
        Document document = sAXReader.read(new File(fileName));
        Iterator entries = document.getRootElement().elementIterator("Column_name");
        List<String> colNames = new ArrayList<String>();
        while(entries.hasNext())
        {
        	Element colName = (Element)entries.next();
        	Iterator colNameIttterator = colName.elementIterator("COL");
        	while(colNameIttterator.hasNext())
        	{
        		 Element column = (Element)colNameIttterator.next();
        		 colNames.add(column.getText());
        	}
        }
        String sql ="INSERT INTO " + tableName + "(";
        for(String str : colNames)
        {
        	sql += str + ",";
        }
        sql = sql.substring(0,sql.length() - 1 );
        sql +=") VALUES (";
        Iterator rowsi = document.getRootElement().elementIterator("Row");
        while(rowsi.hasNext())
        {
        	Element rowelement = (Element)rowsi.next();
        	Iterator cellValue = rowelement.elementIterator("Cell");
        	@SuppressWarnings("unused")
			List<String> rowValues = new ArrayList<String>();
        
        	String sqlinsert = new String(sql);
        	while(cellValue.hasNext())
        	{
        		 Element value = (Element)cellValue.next();
        		 sqlinsert += "'" + value.getText() + "',";
        	}
        	sqlinsert = sqlinsert.substring(0,sqlinsert.length() - 1 );
        	sqlinsert +=")";
            sqls.add(sqlinsert);
        }
    	return sqls;
    }
    private String getLength(String codeName)
    {
        return "10";
    }
    private String getType(String codeName)
    {
        return Constant.VARCHAR_STRING;
    }

}
